package com.zjuh.goodboy.store.item;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;

import com.zjuh.goodboy.items.Item;
import com.zjuh.goodboy.items.ItemUtil;
import com.zjuh.goodboy.misc.DateRange;
import com.zjuh.goodboy.store.jdbc.BaseJdbcDaoSupport;
import com.zjuh.goodboy.util.SqlDateUtil;

public class JdbcItemStore extends BaseJdbcDaoSupport implements ItemStore {
	
	public static final String ITEM_FULL_FIELDS = "site_name, url, name, img_url, description, token, gmt_create, gmt_modified";
	
	private boolean exist(String token) {
		String sql = "select token from goodboy_item_data where token = ?";
		Object dbToken = getJdbcTemplate().query(sql, new Object[] {token} , new ResultSetExtractor() {

			@Override
			public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
				if (rs.next()) {
					return rs.getString("token");
				} else {
					return null;
				}
			}
		});
		return dbToken != null;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Item> queryItemsByDateRange(DateRange dateRange) {
		String sql = "select id, " + ITEM_FULL_FIELDS + " from goodboy_item_data where gmt_modified >= ? and gmt_modified <= ?";
		return (List<Item>) getJdbcTemplate().query(sql, new Object[] { SqlDateUtil.asTimestamp(dateRange.getStart()), SqlDateUtil.asTimestamp(dateRange.getEnd())} , new RowMapper() {

			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				return ItemUtil.rsToItem(rs);
			}

		});
	}

	@Override
	public void saveItems(final List<Item> items) {
		String updateSql = "update goodboy_item_data set site_name = ?, url = ?, name = ?, img_url = ?, description = ?, gmt_modified = now() where token = ?";
		final String insertSql = "insert into goodboy_item_data(" + ITEM_FULL_FIELDS + ") values (?, ?, ?, ?, ?, ?, now(), now())";
		
		for (final Item item : items) {
			if (exist(item.getToken())) {
				getJdbcTemplate().update(updateSql, new PreparedStatementSetter() {

					@Override
					public void setValues(PreparedStatement ps)	throws SQLException {
						setItemValues(ps, item);
					}
				});
			} else {
				getJdbcTemplate().update(insertSql, new PreparedStatementSetter() {

					@Override
					public void setValues(PreparedStatement ps)	throws SQLException {
						setItemValues(ps, item);
					}
				});
			}
		}
		
	}

	private void setItemValues(PreparedStatement ps, Item item) throws SQLException {
		ps.setString(1, item.getSiteName());
		ps.setString(2, item.getUrl());
		ps.setString(3, item.getName());
		ps.setString(4, item.getImgUrl());
		ps.setString(5, item.getDescription());
		ps.setString(6, item.getToken());
	}
	
	@Override
	public void saveItem(final Item item) {
		saveItems(Arrays.asList(item));
	}

}
